<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class System_model extends CI_Model {

	function __construct()
	{
		parent::__construct();
    }

	function getViewKontrolData($kodeProdi)
    {
      $query = "SELECT * FROM view_kontrol WHERE KODE_PRODI =".$kodeProdi." LIMIT 1";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        $data = $query->row();
        return $data;
      }
      else
        return FALSE;
    }

    function getViewKontrolData_limit1()
    {
      $query = "SELECT * FROM view_kontrol LIMIT 1";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        $data = $query->row();
        return $data;
      }
      else
        return FALSE;
    }

    function getSelectOptionsValueSma()
	{
	    $query = "SELECT * FROM asal_sma";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->result();
        }
        else $data = NULL;
        return $data;
	}

    function getSelectOptionsValueAgama()
	{
	    $query = "SELECT * FROM agama";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->result();
        }
        else $data = NULL;
        return $data;
	}

    function getSelectOptionsValueSemester()
	{
	    $query = "SELECT * FROM semester ORDER BY `ID` ASC";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->result();
        }
        else $data = NULL;
        return $data;
	}

    function getSelectOptionsValueTahunAjaran()
	{
	    $query = "SELECT * FROM tahun_ajaran ORDER BY `ID` DESC";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->result();
        }
        else $data = NULL;
        return $data;
	}

    function getSelectOptionsValueMahasiswa($prodi)
    {
        $query = "SELECT * FROM view_data_mahasiswa WHERE KODE_PRODI=$prodi AND LEVEL<>99 ORDER BY NAMA";

        $query = $this->db->query($query);

        if($query->num_rows()>0)
          $data = $query->result();
        else
          $data = NULL;

        return $data;
     }

     function getValueHari()
	 {
	    $query = "SELECT * FROM hari";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->result();
        }
        else $data = NULL;
        return $data;
	 }

     function maxHari()
	 {
	    $query = "SELECT MAX(ID) AS ID_MAX FROM hari";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->row();
        }
        else $data = NULL;
        return $data;
	 }

     function getJamKuliah()
	 {
	    $query = "SELECT * FROM waktu_kuliah ORDER BY WAKTU_MULAI, WAKTU_SELESAI ASC";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->result();
        }
        else $data = NULL;
        return $data;
	 }

     function getJamUjian()
	 {
	    $query = "SELECT * FROM waktu_ujian ORDER BY WAKTU_MULAI, WAKTU_SELESAI ASC";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->result();
        }
        else $data = NULL;
        return $data;
	 }

     function getSelectOptionsValueStatusPeriode()
	 {
	    $query = "SELECT * FROM status_periode";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->result();
        }
        else $data = NULL;
        return $data;
	 }

     function getSubstringNim()
	 {
	    $query = "SELECT DISTINCT SUBSTRING(NIM,4,2) AS ANGKATAN FROM mahasiswa_iain ORDER BY ANGKATAN DESC";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->result();
        }
        else $data = NULL;
        return $data;
	 }

     function getSubstringNimProdi($kodeprodi)
	 {
	    $query = "SELECT DISTINCT SUBSTRING(NIM,4,2) AS ANGKATAN FROM mahasiswa_iain WHERE KODE_PRODI ='{$kodeprodi}' ORDER BY ANGKATAN DESC";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->result();
        }
        else $data = NULL;
        return $data;
	 }

     function addTahunAjaran() {
        $query = $this->db->query("SELECT MAX(ID) AS MAX_ID FROM tahun_ajaran");
        $query = $query->row();
        $max_id = $query->MAX_ID;

        $tahun_baru = $max_id+1;
        $tahun_ajaran_baru = implode('/', array($tahun_baru, $tahun_baru+1));

        $query = $this->db->query("INSERT INTO tahun_ajaran VALUES('{$tahun_baru}', '{$tahun_ajaran_baru}')");

	}

    function getProdi()
	{
	    $query = "SELECT * FROM prodi ORDER BY KODE ASC";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->result();
        }
        else $data = NULL;
        return $data;
	}

    function getFakultas()
	{
	    $query = "SELECT * FROM fakultas ORDER BY ID ASC";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->result();
        }
        else $data = NULL;
        return $data;
	}

    function getSubstringNimLimit1($kodeprodi)
	{
	    $query = "SELECT DISTINCT SUBSTRING(NIM,4,2) AS ANGKATAN FROM mahasiswa_iain WHERE KODE_PRODI ='{$kodeprodi}' AND SUBSTRING(NIM,4,2) < 90 ORDER BY ANGKATAN DESC LIMIT 1";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->row();
        }
        else $data = NULL;
        return $data;
	}

    function getSemesterSkrg($nim,$tahun,$smt)
    {
        $angkatan="20".substr($nim,3,2);
        $selisih=$tahun-$angkatan;
        $semester=($selisih*2)+$smt;
        switch($semester)
        {
          case 1:
            $hsl="I";
            break;
          case 2:
            $hsl="II";
            break;
          case 3:
            $hsl="III";
            break;
          case 4:
            $hsl="IV";
            break;
          case 5:
            $hsl="V";
            break;
          case 6:
            $hsl="VI";
            break;
          case 7:
            $hsl="VII";
            break;
          case 8:
            $hsl="VIII";
            break;
          case 9:
            $hsl="IX";
            break;
          case 10:
            $hsl="X";
            break;
          case 11:
            $hsl="XI";
            break;
          case 12:
            $hsl="XII";
            break;
          case 13:
            $hsl="XIII";
            break;
          case 14:
            $hsl="XIV";
            break;
          default:
            $hsl="-";
            break;
        }
        return $hsl;
    }

    function ekspor_data_model($query,$link)
    {
        $this->load->dbutil();
        $this->load->helper('download');

        //$query = $this->session->userdata('last_query');
        $judul = $link.'.csv';
        //$query = $_SESSION['last_query'];
        //$judul = $_SESSION['query_name'].'.csv';
        //echo $query;
        $query = $this->db->query($query);
        $delimiter = ",";
        $newline = "\r\n";
        $data=$this->dbutil->csv_from_result($query, $delimiter, $newline);
        force_download($judul, $data);
    }

    function getViewUser($username,$kodeProdi)
    {
      $query = "SELECT * FROM view_user WHERE USERNAME='$username' AND PRODI='$kodeProdi'";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        $data = $query->row();
        return $data;
      }
      else
        return FALSE;
    }

    function getAngkatan()
	{
        $query = "SELECT DISTINCT SUBSTRING(NIM,4,2) AS ANGKATAN FROM mahasiswa ORDER BY ANGKATAN DESC";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->result();
        }
        else $data = NULL;
        return $data;
	}

    function insertLogsistem($user, $controller, $model, $operasi)
    {
        $query = "INSERT INTO logsistem VALUES (
                    NULL,
					now(),
					'{$user}',
					'{$controller}',
					'{$model}',
                    '{$operasi}'
		         )";

        $query = $this->db->query($query);

        if($query)
        {
          return TRUE;
        }
        else
        {
          return FALSE;
        }
    }

     function getTahunKurikulum()
    {
        $query = "SELECT * FROM kurikulum";

        $query = $this->db->query($query);

        if($query->num_rows()>0)
          $data = $query->result();
        else
          $data = NULL;

        return $data;
    }

    function getPengumuman($id)
    {
        $query = "SELECT * FROM pengumuman WHERE (SUB=$id OR SUB=99) AND PUB=1 ORDER BY TANGGAL DESC";
        $query = $this->db->query($query);

        if($query->num_rows()>0){
          $data = $query->result();
        }
        else $data = NULL;
        return $data;
    }

}

